//
//  NovelDataBase.m
//  数据库
//
//  Created by 0.0 on 15-3-31.
//  Copyright (c) 2015年 dlou. All rights reserved.
//

#import "DownLoadDataBase.h"
#import "DownLoadModel.h"
#import "DownLoadDB.h"

@implementation DownLoadDataBase

+ (void)insertWithModel:(DownLoadModel *)model
{
    
    // 第一步:编写Sql语句
    NSString *sql = [NSString stringWithFormat:@"insert into VideoDown values('%@', '%@', '%@', '%@', %f)", model.videoName, model.videoURLPath, model.videoImageURL, model.vegetableName, 0.0];
    
    // 第二步:获得数据库指针
    sqlite3 *getConnection = [DownLoadDB openDB];
    
    // 第三步:执行Sql语句
    int result = sqlite3_exec(getConnection, [sql UTF8String], nil, nil, nil); // 返回一个整形 看看是否执行成功 (往某个数据库执行sql字符串语句
    if (result == SQLITE_OK) {
        NSLog(@"执行成功");
    }
    else {
        NSLog(@"执行失败");
    }
    
}

+ (void)deleteWithTable:(NSString *)videoName
{
    NSString *sql = [NSString stringWithFormat:@"delete from VideoDown where videoName = '%@' ", videoName];
    sqlite3 *get = [DownLoadDB openDB];
    
    int result = sqlite3_exec(get, [sql UTF8String], nil, nil, nil);
    NSLog(@"%d", result);
}

+ (void)updateWithStateNum:(CGFloat)num videoName:(NSString *)videoName
{
    NSString *sql = [NSString stringWithFormat:@"update VideoDown set videoDownNum = %0.2f where vegetableName = '%@' ", num, videoName];
    sqlite3 *get = [DownLoadDB openDB];
    
    int result = sqlite3_exec(get, [sql UTF8String], nil, nil, nil);
    NSLog(@"update:%d", result);
}


+ (NSArray *)searchNovel
{

    NSString *str = [NSString stringWithFormat:@"select videoName, videoURLPath, videoImageURL, vegetableName, videoDownNum from VideoDown"];
    sqlite3 *get = [DownLoadDB openDB];
    
    // 定义数据库查询结果
    sqlite3_stmt *stmt;
    
    // 执行sql语句,并把查询到得结果赋值到stmt中
    int result = sqlite3_prepare_v2(get, [str UTF8String], -1, &stmt, nil);// 第三个参数是sql语句的长度, -1代表无限制长, 第四个参数是放到什么地方
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) { // 一行一行查找, 是否有下一行(执行一遍查询一行)
            const unsigned char *videoName = sqlite3_column_text(stmt, 0); // 取第0个列, 也就是第一列
            const unsigned char *videoPath = sqlite3_column_text(stmt, 1);
            const unsigned char *videoImage = sqlite3_column_text(stmt, 2);
            const unsigned char *vegetableName = sqlite3_column_text(stmt, 3);
            CGFloat num = sqlite3_column_double(stmt, 4);
            
            NSString *videoName2 = [NSString stringWithUTF8String:(const char *)videoName]; // 将const unsigned char 类型的转换成字符串
            NSString *videoPath2 = [NSString stringWithUTF8String:(const char *)videoPath];
            NSString *videoImage2 = [NSString stringWithUTF8String:(const char *)videoImage];
            NSString *vegetableName2 = [NSString stringWithUTF8String:(const char *)vegetableName];
            
            DownLoadModel *model = [DownLoadModel downLoadWithVideoName:videoName2 videoPath:videoPath2 videoImage:videoImage2 vegetable:vegetableName2 stateNum:num];
            
            [array addObject:model];
        }
    }
    
    sqlite3_finalize(stmt); // 将stmt释放掉
    
    return array;
}


+ (DownLoadModel *)searchWithId:(NSString *)vegetableName
{
    
    NSString *str = [NSString stringWithFormat:@"select * from VideoDown where vegetableName = '%@'", vegetableName];
    sqlite3 *get = [DownLoadDB openDB];
    
    // 定义数据库查询结果
    sqlite3_stmt *stmt;
    
    // 执行sql语句,并把查询到得结果赋值到stmt中
    int result = sqlite3_prepare_v2(get, [str UTF8String], -1, &stmt, nil);// 第三个参数是sql语句的长度, -1代表无限制长, 第四个参数是放到什么地方
  
    if (result == SQLITE_OK) {
        if (sqlite3_step(stmt) == SQLITE_ROW) { // 一行一行查找, 是否有下一行(执行一遍查询一行)
            const unsigned char *videoName = sqlite3_column_text(stmt, 0); // 取第0个列, 也就是第一列
            const unsigned char *videoPath = sqlite3_column_text(stmt, 1);
            const unsigned char *videoImage = sqlite3_column_text(stmt, 2);
            const unsigned char *vegetableName = sqlite3_column_text(stmt, 3);
            CGFloat num = sqlite3_column_double(stmt, 4);
            
            
            NSString *videoName2 = [NSString stringWithUTF8String:(const char *)videoName]; // 将const unsigned char 类型的转换成字符串
            NSString *videoPath2 = [NSString stringWithUTF8String:(const char *)videoPath];
            NSString *videoImage2 = [NSString stringWithUTF8String:(const char *)videoImage];
            NSString *vegetableName2 = [NSString stringWithUTF8String:(const char *)vegetableName];
            
            DownLoadModel *model = [DownLoadModel downLoadWithVideoName:videoName2 videoPath:videoPath2 videoImage:videoImage2 vegetable:vegetableName2 stateNum:num];
            
            sqlite3_finalize(stmt); // 将stmt释放掉
            
            return model;
        }
    }
    
    sqlite3_finalize(stmt); // 将stmt释放掉
    
    return nil;
}

//+ (DownLoadDataBase *)downLoadDataBase
//{
//    DownLoadDataBase *down = [[DownLoadDataBase alloc] init];
//    
//    return [down autorelease];
//}


@end
